﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using EntLibSample.DALCommon;
using EntLibSample.EntityModel;

namespace EntLibSample.DAL
{
    public class DALAddress
    {
        private Database sqlDB = DatabaseFactory.CreateDatabase();
        private DbCommand dbCommand = null;
        private DbTransaction dbTransaction = null;
        private SqlExecution sqlExecution = new SqlExecution();

        public DALAddress(DbCommand command, DbTransaction transaction = null)
        {
            dbCommand = command;
            dbTransaction = transaction;
            sqlExecution.dbCommand = command;
            sqlExecution.dbTransaction = transaction;
            sqlExecution.sqlDB = sqlDB;
        }

        public int AddAddress(Address address)
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "INSERT_ADDRESS";
            sqlDB.AddOutParameter(dbCommand, "@AddressID", DbType.Int32, 32);
            sqlDB.AddInParameter(dbCommand, "@AddressLine1", DbType.AnsiString, address.AddressLine1);
            sqlDB.AddInParameter(dbCommand, "@AddressLine2", DbType.AnsiString, address.AddressLine2);
            sqlDB.AddInParameter(dbCommand, "@City", DbType.AnsiString, address.City);
            sqlDB.AddInParameter(dbCommand, "@CountryRegion", DbType.AnsiString, address.CountryRegion);
            sqlDB.AddInParameter(dbCommand, "@PostalCode", DbType.AnsiString, address.PostalCode);
            sqlDB.AddInParameter(dbCommand, "@StateProvince", DbType.AnsiString, address.StateProvince);
            return sqlExecution.ExecuteNonQuery();
        }

        public int UpdateAddress(Address address)
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "UPDATE_ADDRESS";
            sqlDB.AddInParameter(dbCommand, "@AddressID", DbType.Int32, address.AddressID);
            sqlDB.AddInParameter(dbCommand, "@AddressLine1", DbType.AnsiString, address.AddressLine1);
            sqlDB.AddInParameter(dbCommand, "@AddressLine2", DbType.AnsiString, address.AddressLine2);
            sqlDB.AddInParameter(dbCommand, "@City", DbType.AnsiString, address.City);
            sqlDB.AddInParameter(dbCommand, "@CountryRegion", DbType.AnsiString, address.CountryRegion);
            sqlDB.AddInParameter(dbCommand, "@PostalCode", DbType.AnsiString, address.PostalCode);
            sqlDB.AddInParameter(dbCommand, "@StateProvince", DbType.AnsiString, address.StateProvince);
            return sqlExecution.ExecuteNonQuery();
        }

        public int DeleteAddress(Address address)
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "DELETE_ADDRESS";
            sqlDB.AddInParameter(dbCommand, "@AddressID", DbType.Int32, address.AddressID);
            return sqlExecution.ExecuteNonQuery();
        }

        public Address FindAnAddress(Address address)
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "SELECT_AN_ADDRESS";
            sqlDB.AddInParameter(dbCommand, "@AddressID", DbType.Int32, address.AddressID);
            DataSet ds = sqlExecution.ExecuteDataSet();
            return DataMapper.MapDataTableToSingleRow<Address>(ds.Tables[0]);
        }

        public List<Address> GetAllAddress()
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "SELECT_ALL_ADDRESS";
            DataSet ds = sqlExecution.ExecuteDataSet();
            return DataMapper.MapDataTableToObjectList<Address>(ds.Tables[0]);
        }

        public List<Address> GetAddressByCity(Address address)
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "SELECT_ALL_ADDRESS";
            sqlDB.AddInParameter(dbCommand, "@City", DbType.AnsiString, address.City);
            DataSet ds = sqlExecution.ExecuteDataSet();
            return DataMapper.MapDataTableToObjectList<Address>(ds.Tables[0]);
        }
    }
}
